with way as (
select waybill_no
        ,input_time 
        ,pick_network_code
        ,waybill_source_code
        ,waybill_source_name
from jms_ods.yl_oms_oms_waybill
where dt = '{{ execution_date | cst_ds }}' 
and to_date(input_time) = '{{ execution_date | cst_ds }}'
and is_delete = 1
and is_void = 0
),
network as (
select 
        code
        ,name
        ,franchisee_code
        ,franchisee_name
        ,agent_code
        ,agent_name
        ,provider_desc
        ,city_desc
        ,area_desc 
from jms_dim.dim_sys_network_detail_dt
where dt = '{{ execution_date | cst_ds }}'
)
insert overwrite table jms_dwd.dwd_waybill_detail_dt partition(dt)
select   waybill_no             as waybill_no          
        ,to_date(input_time)    as input_date          
        ,input_time             as input_time          
        ,pick_network_code      as pick_network_code   
        ,network.name           as pick_network_name                
        ,waybill_source_code    as waybill_source_code 
        ,waybill_source_name    as waybill_source_name 
        ,network.provider_desc  as sender_province_name
        ,network.city_desc      as sender_city_name    
        ,network.area_desc      as sender_area_name    
        ,franchisee_code        as franchisee_code     
        ,franchisee_name        as franchisee_name     
        ,agent_code             as agent_code          
        ,agent_name             as agent_name          
        ,to_date(input_time)    as dt
from way 
left join network on coalesce(way.pick_network_code,waybill_no) = network.code;